﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace ProductSimilar
{
    public class wnxd_ProductSimilar
    {
        private static Database database = DatabaseFactory.CreateDatabase();
        public List<SimilarInfo> GetSimilarProductIds(int ProductId)
        {
            List<SimilarInfo> list = new List<SimilarInfo>();
            DbCommand cmd = database.GetSqlStringCommand(string.Format("select ProductName,ProductId,ProductCode from Hishop_Products where ProductId in (select ProductId from wnxd_ProductSimilar where SimilarId = (select SimilarId from wnxd_ProductSimilar where ProductId = {0}))", ProductId));
            using (IDataReader reader = database.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    SimilarInfo t = new SimilarInfo();
                    t.ProductName = reader["ProductName"].ToString();
                    t.ProductCode = reader["ProductCode"].ToString();
                    t.ProductId = (int)reader["ProductId"];
                    list.Add(t);
                }
            }
            return list;
        }
        public int GetSimilarCount(int ProductId)
        {
            int ret = 0;
            DbCommand cmd = database.GetSqlStringCommand(string.Format("select count(*) from wnxd_ProductSimilar where SimilarId = (select SimilarId from wnxd_ProductSimilar where ProductId = {0})", ProductId));
            using (IDataReader reader = database.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    ret = (int)reader[0];
                }
            }
            return ret;
        }
        public bool AddSimilarProduct(int now, string ProductCode)
        {
            return AddSimilarProduct(now, Code2Id(ProductCode));
        }
        public bool AddSimilarProduct(int now, int ProductId)
        {
            bool ret = false;
            DbConnection conn = database.CreateConnection();
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            DbCommand cmd = database.GetSqlStringCommand(string.Format("select count(*) from wnxd_ProductSimilar where ProductId = {0}", now));
            int count = (int)database.ExecuteScalar(cmd);
            if (count == 0)
            {
                cmd = database.GetSqlStringCommand(string.Format("select count(*) from wnxd_ProductSimilar where ProductId = {0};select SimilarId from wnxd_ProductSimilar where ProductId = {0}", ProductId));
                using (IDataReader reader = database.ExecuteReader(cmd))
                {
                    if (reader.Read())
                    {
                        string id;
                        if ((int)reader[0] == 0)
                        {
                            id = Guid.NewGuid().ToString();
                            cmd = database.GetSqlStringCommand(string.Format("insert into wnxd_ProductSimilar (SimilarId,ProductId) values ('{0}', {1})", id, now));
                            if (database.ExecuteNonQuery(cmd) > 0)
                            {
                                cmd = database.GetSqlStringCommand(string.Format("insert into wnxd_ProductSimilar (SimilarId,ProductId) values ('{0}', {1})", id, ProductId));
                                if (database.ExecuteNonQuery(cmd) > 0)
                                {
                                    ret = true;
                                }
                            }
                        }
                        else
                        {
                            reader.NextResult();
                            if (reader.Read())
                            {
                                id = reader["SimilarId"].ToString();
                                cmd = database.GetSqlStringCommand(string.Format("insert into wnxd_ProductSimilar (SimilarId,ProductId) values ('{0}', {1})", id, now));
                                if (database.ExecuteNonQuery(cmd) > 0)
                                {
                                    ret = true;
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                cmd = database.GetSqlStringCommand(string.Format("select count(*) from wnxd_ProductSimilar where ProductId = {0};select SimilarId from wnxd_ProductSimilar where ProductId = {1}", ProductId, now));
                using (IDataReader reader = database.ExecuteReader(cmd))
                {
                    if (reader.Read())
                    {
                        if ((int)reader[0] == 0)
                        {
                            reader.NextResult();
                            string id;
                            if (reader.Read())
                            {
                                id = reader["SimilarId"].ToString();
                            }
                            else
                            {
                                id = Guid.NewGuid().ToString();
                            }
                            cmd = database.GetSqlStringCommand(string.Format("insert into wnxd_ProductSimilar (SimilarId,ProductId) values ('{0}', {1})", id, ProductId));
                            if (database.ExecuteNonQuery(cmd) > 0)
                            {
                                ret = true;
                            }
                        }
                    }
                }
            }
            if (ret)
            {
                tran.Commit();
            }
            else
            {
                tran.Rollback();
            }
            conn.Close();
            return ret;
        }
        public bool DeleteSimilarProduct(string ProductCode)
        {
            DbCommand cmd = database.GetSqlStringCommand(string.Format("delete wnxd_ProductSimilar where ProductId = (select ProductId from Hishop_Products where ProductCode = '{0}')", ProductCode));
            return database.ExecuteNonQuery(cmd) > 0;
        }
        public bool DeleteSimilarProduct(int ProductId)
        {
            DbCommand cmd = database.GetSqlStringCommand(string.Format("delete wnxd_ProductSimilar where ProductId = ", ProductId));
            return database.ExecuteNonQuery(cmd) > 0;
        }
        public int Code2Id(string ProductCode)
        {
            int ret = 0;
            DbCommand cmd = database.GetSqlStringCommand(string.Format("select ProductId from Hishop_Products where ProductCode = '{0}'", ProductCode));
            using (IDataReader reader = database.ExecuteReader(cmd))
            {
                if (reader.Read())
                {
                    ret = (int)reader["ProductId"];
                }
            }
            return ret;
        }
    }
    public struct SimilarInfo
    {
        public string ProductName { get; set; }
        public string ProductCode { get; set; }
        public int ProductId { get; set; }
    }
}
